package com.songshuai.webapi.crm.util;

import com.songshuai.webapi.baseapi.annotations.ExcelField;
import com.songshuai.webapi.baseapi.utils.DateUtil;
import com.songshuai.webapi.crm.constant.CrmConstant;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 *
 * @Description: excel导出封装类
 * @author hst
 * @date 2019年1月7日
 */
@Slf4j
public class ExcelUtil {
    // 生成excel，list导出的数据，list里的实体class，sumData合计数据
    public static <Q> XSSFWorkbook createExcel(List<Q> list, Class<Q> cls, Q sumData) {
        XSSFWorkbook wb = new XSSFWorkbook();
        // 添加合计数据
        if (sumData != null) {
            list.add(sumData);
        }
        Field[] fields = cls.getDeclaredFields();
        //获取导出实体类的ExcelField注解title，比如姓名、手机号.....
        ArrayList<String> headList =getHeadList(fields);
        XSSFCellStyle style = getCellStyle(wb);
        XSSFSheet sheet = wb.createSheet();
        // 设置Excel表的第一行即表头
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headList.size(); i++) {
            XSSFCell headCell = row.createCell(i);
            headCell.setCellType(Cell.CELL_TYPE_STRING);
            headCell.setCellStyle(style);// 设置表头样式
            headCell.setCellValue(String.valueOf(headList.get(i)));
            // sheet.autoSizeColumn((short) i);// 设置单元格自适应
           // sheet.setColumnWidth(0, 15 * 256);
            sheet.autoSizeColumn(i, true);// 设置单元格自适应列宽
        }
        if(CollectionUtils.isEmpty(list)){
            //数据为空，返回空的EXCEL
            return wb;
        }
        //将数据一个一个单元格、一行行写入excel
        for (int i = 0; i < list.size(); i++) {
            XSSFRow rowdata = sheet.createRow(i + 1);// 创建数据行
            Q q = list.get(i);
            Field[] ff = q.getClass().getDeclaredFields();
            int j = 0;//单元格cell在该行的index位置
            for (Field f : ff) {
                ExcelField field = f.getAnnotation(ExcelField.class);
                if (field == null) {
                    continue;
                }
                f.setAccessible(true);
               // Object obj = f.get(q);
                //通过反射获取bean对象相应字段Field的值
                PropertyDescriptor pd = null;
                try {
                    pd = new PropertyDescriptor(f.getName(), q.getClass());
                } catch (IntrospectionException e) {
                    log.error("ExcelUtil.createExcel()",e);
                    continue;
                }
                Method method = pd.getReadMethod();//获得get方法
                Object obj = null;//执行get方法返回一个Object
                try {
                    obj = method.invoke(q);
                } catch (IllegalAccessException e) {
                    log.error("ExcelUtil.createExcel()",e);
                    continue;
                } catch (InvocationTargetException e) {
                    log.error("ExcelUtil.createExcel()",e);
                    continue;
                }
                XSSFCell cell = rowdata.createCell(j);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                //将值写入excel单元格
                cell=setCellValue(cell,obj);
                j++;
            }
        }
        if (sumData != null) {
            int rowIndex = list.size();
            XSSFRow sumRow = sheet.getRow(rowIndex);
            XSSFCell sumCell = sumRow.getCell(0);
            sumCell.setCellStyle(style);
            sumCell.setCellValue("合计");
        }
        // 必须在单元格设值以后进行
        // 设置为根据内容自动调整列宽
        for (int k = 0; k < list .size(); k++) {
            sheet.autoSizeColumn(k);
        }
        // 处理中文不能自动调整列宽的问题
        setSizeColumn(sheet, list.size());
        return wb;
    }

    // 导出
    public static void writeExcel(HttpServletResponse response, String fileName, XSSFWorkbook wb) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
        OutputStream ouputStream = null;
        try {
            ouputStream = response.getOutputStream();
            wb.write(ouputStream);
        } finally {
            ouputStream.close();
        }
    }

    // 表头样式
    public static XSSFCellStyle getCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)12);// 设置字体大小
        font.setColor(HSSFColor.WHITE.index);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
        style.setFillForegroundColor(HSSFColor.BLUE.index);// 设置背景色
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);// 让单元格居中
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        //style.setWrapText(true);// 设置自动换行
        style.setFont(font);
        return style;
    }

    // 自适应宽度(中文支持)
    public static void setSizeColumn(XSSFSheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                XSSFRow currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    XSSFCell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

    /**
     * 单元格cell赋值
     * @param cell
     * @param obj
     * @return
     */
    public static XSSFCell setCellValue(XSSFCell cell,Object obj){
        if(null==obj){
            cell.setCellValue(CrmConstant.EMPTY);
            return cell;
        }
        try{
            if (obj instanceof Integer) {
                // 当数字时
                cell.setCellValue((Integer)obj);
                // 将序号替换为123456
                /*if (j == 0)
                    cell.setCellValue(i + 1);*/
            }else if (obj instanceof String) {
                // 当为字符串时
                cell.setCellValue((String) obj);
            }else if (obj instanceof Boolean){
                // 当为布尔时
                cell.setCellValue((Boolean)obj);
            }else if (obj instanceof Date){
                // 当为时间时
                cell.setCellValue(DateUtil.ChangeDateToString((Date)obj, DateUtil.dateFormat));
            }else if (obj instanceof Calendar){
                // 当为时间时
                cell.setCellValue((Calendar)obj);
            }else if (obj instanceof Double){
                    // 当为小数时
                    cell.setCellValue((Double)obj);
            }
        }catch (Exception e){
            log.error("ExcelUtil.setCellValue()",e);
            cell.setCellValue(CrmConstant.EMPTY);
            return cell;
        }
        return cell;
    }

    /**
     * 获取导出实体类的ExcelField注解title
     * @param fields
     * @return
     */
    public static ArrayList<String> getHeadList(Field[] fields){
        ArrayList<String> headList = new ArrayList<String>();
        for (Field f : fields) {
            ExcelField field = f.getAnnotation(ExcelField.class);
            if (field != null) {
                headList.add(field.title());
            }
        }
        return headList;
    }


}
